package com._520it.day2._02_batch;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

import com._520it.util.JdbcUtil;

//测试批量操作
//往account表中插入2000条数据
public class BatchTest {

	// 没有使用批量操作
	@Test
	public void testStatement() throws Exception {
		long begin = System.currentTimeMillis();
		Connection conn = JdbcUtil.getConn();
		Statement st = conn.createStatement();

		for (int i = 0; i < 2001; i++) {
			String sql = "INSERT INTO account (balance) VALUES (" + i + ")";
			st.executeUpdate(sql);
		}
		JdbcUtil.close(conn, st, null);
		System.out.println(System.currentTimeMillis() - begin);
	}

	// 使用批量操作
	@Test
	public void testSatementByBatch() throws Exception {
		long begin = System.currentTimeMillis();
		Connection conn = JdbcUtil.getConn();
		Statement st = conn.createStatement();

		for (int i = 0; i < 2001; i++) {
			String sql = "INSERT INTO account (balance) VALUES (" + i + ")";
			st.addBatch(sql);// 添加到批量操作中去
			if (i % 200 == 0) {
				st.executeBatch();// 执行批量操作
				st.clearBatch();// 清楚缓存
			}
		}
		JdbcUtil.close(conn, st, null);
		System.out.println(System.currentTimeMillis() - begin);
	}

	// 没有使用批量操作
	@Test
	public void testPreparedStatement() throws Exception {
		String sql = "INSERT INTO account (balance) VALUES (?)";
		long begin = System.currentTimeMillis();
		Connection conn = JdbcUtil.getConn();
		PreparedStatement ps = conn.prepareStatement(sql);

		for (int i = 0; i < 2001; i++) {
			ps.setInt(1, i);
			ps.executeUpdate(sql);
		}
		JdbcUtil.close(conn, ps, null);
		System.out.println(System.currentTimeMillis() - begin);
	}

	// 使用批量操作
	@Test
	public void testPreparedSatementByBatch() throws Exception {
		String sql = "INSERT INTO account (balance) VALUES (?)";
		long begin = System.currentTimeMillis();
		Connection conn = JdbcUtil.getConn();
		PreparedStatement ps = conn.prepareStatement(sql);

		for (int i = 0; i < 2001; i++) {
			ps.setInt(1, i);
			ps.addBatch();
			if (i % 200 == 0) {
				ps.executeBatch();
				ps.clearBatch();
				ps.clearParameters();//清除参数，因为有占位符
			}
		}
		JdbcUtil.close(conn, ps, null);
		System.out.println(System.currentTimeMillis() - begin);
	}
}
